﻿using System.Linq;
using System.Text;
using System.Collections.Generic;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Model;

namespace DAL
{
    public class CategoryDAL
    {

        /// <summary>
        /// 获取所有标签
        /// </summary>
        /// <returns></returns>
        public List<Category> GetList()
        {
            return DBUtility.MySqlHelper.ExecuteDataReader<Category>("select * from Category");
        }

        /// <summary>
        /// 获取一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Category GetModel(string id)
        {
            return DBUtility.MySqlHelper.ExecuteDataReaderModel<Category>("select * from Category where cate_id=@id", new MySqlParameter("@id", id));
        }

        /// <summary>
        /// 分类名称获取分类id
        /// </summary>
        /// <param name="catename"></param>
        /// <returns></returns>
        public int GetCateID(string catename)
        {
            return DBUtility.MySqlHelper.ExecuteScalar<int>("select cate_id from Category where cate_name=@catename", new MySqlParameter("@catename", catename) );
        }

        /// <summary>
        /// 获取自定义数据
        /// </summary>
        /// <param name="columns">列名</param>
        /// <param name="where">条件 </param>
        /// <returns></returns>
        public List<dynamic> GetList(string column, string where)
        {
            string sql = string.Format("select {0} from Category where 1=1 {1}", column, where);
            return DBUtility.MySqlHelper.ExecuteDynamic(sql);
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(Category model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@cate_name",model.cate_name),
                new MySqlParameter("@cate_count",model.cate_count),
                new MySqlParameter("@cate_mate",model.cate_mate)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery(" insert into Category (cate_name,cate_count,cate_mate) Values(@cate_name,@cate_count,@cate_mate)", para);
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(Category model)
        {
            MySqlParameter[] para = {
                new MySqlParameter("@cate_name",model.cate_name),
                new MySqlParameter("@cate_mate",model.cate_mate),
                new MySqlParameter("@cate_id",model.cate_id)
            };
            return DBUtility.MySqlHelper.ExecuteNonQuery("update Category set cate_name=@cate_name,cate_mate=@cate_mate where cate_id=@cate_id", para);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int delete(int id)
        {
            return DBUtility.MySqlHelper.ExecuteNonQuery("delete from Category where cate_id=@id", new MySqlParameter("@id", id));
        }

        /// <summary>
        /// 获取文章数最多的分类（5个）
        /// </summary>
        /// <returns></returns>
        public List<Category> BlogCate()
        {
            return DBUtility.MySqlHelper.ExecuteDataReader<Category>("select * from Category order by cate_count desc limit 5");
        }

        /// <summary>
        /// 更新分类数量
        /// </summary>
        /// <param name="cateid"></param>
        /// <returns></returns>
        public int UpdateCateCount(int cateid)
        {
            return DBUtility.MySqlHelper.ExecuteNonQuery("update Category set cate_count=(select count(*) from blog where  blog_cate like '%【"+ cateid + "】%') where cate_id="+ cateid);
        }


    }
}
